"""处理数据层，将前端的数据传入到本层进行处理，处理过程和结果将显示在控制台界面"""
import time
import os
import re
import math
from openpyxl import load_workbook
from openpyxl import Workbook
from statistics import *
from openpyxl.utils import get_column_letter
from commonData import ComData as cd
"""
{
    '1月1-1月10日20-20时累计降水量':
    {
        '50527': ['50527', long, lat, high, year1, year2, year3]
        '50557' : [
    }
}
{
    '1月1-1月10日20-20时累计降水量':
    {
        '50527': 统计值
        '50557' : 统计值
    }
}

{
    '平均本站气压' : ['1月1日至28日平均值', '1月1日至28日最小值']
}


self.data = {
'2014':{
    '50527':[value1, value2, value3, value4],
    '50557':[value1, value2, value3, value4],
    #有些数据只在某一文件有，假设为22222，那么
    '22222': [value1, ...] 没有的值则不考虑，最终的结果就是一个值
}
}
"""
class Data:
    def __init__(self):
        self.month_pre = None
        self.month_next = None
        self.day1 = None
        self.day2 = None
        self.day_leap = self.day2
        self.data = {}  # 全部数据所在的集合
        self.other_infos = {}
        self.letter = []
        self.first_year_letter = 'A'
        self.data_type = ""
        '''处理区站号的五个变量'''
        if cd.file_out != None and os.path.exists(cd.file_out) == False:
            os.mkdir(cd.file_out)


    def get_abnormal(fun):
        def abnormal(self, *args, **kwargs):
            try:
                return fun(self, *args, **kwargs)
            except Exception as e:
                print("异常信息:{},异常方法:{}".format(e, fun.__name__))

        return abnormal  # 没有返回值


    @get_abnormal
    def cal_lose(self):
        for year in self.data.keys():    # 第一层是年
            for station in self.other_infos.keys():
                if station not in self.data[year].keys():
                    self.data[year][station] = []    # 设为空

    @get_abnormal
    def start(self):
        for sheet_name, values in cd.targetInfo.items():
            if '二次' not in sheet_name:  # 只处理非二次的数据
                name = sheet_name           # 气象指标：日最高本站气压，日最高本站气压质量控制码tec..
                for value in values:        # value: 2月4日至8月24日平均值, 要提取出2 4 8 24, 然后统计这一阶段的值
                    self.data = {}   # 重置data值
                    self.other_infos = {}  # 重置ohterinfos
                    print("正在处理 {} {}".format(name, value))
                    temp = re.findall(r'(\d+)月(\d+)日至(\d+)月(\d+)日(\w+)', value)[0]
                    self.month_pre, self.day1, self.month_next, self.day2, self.data_type = int(temp[0]), int(temp[1]), int(temp[2]), int(temp[3]), temp[4]
                    data_name = "{}月{}日至{}月{}日{}{}".format(self.month_pre, self.day1, self.month_next, self.day2, name, self.data_type)   # 用于data的字典的键中的key
                    self.day_leap = self.day2    # 决定是否要处理闰年
                    years, TYPE = Data.getYear(self)   # 从小到大获取年份
                    # print(years, TYPE)
                    for year in years:
                        self.data[year] = {}
                        Data.processDataNew(self, year, TYPE, data_name, name)

                    """处理缺失数据"""
                    Data.cal_lose(self)  # 处理缺失数据
                    '''存入文件中'''
                    print("写入文件中...")
                    wb = Workbook()
                    wb.active
                    ws = wb['Sheet']
                    wb.remove_sheet(ws)
                    # 建立新的ws
                    ws = wb.create_sheet(data_name)  # 以data_name作为sheet的值
                    ws.freeze_panes = 'A2'
                    if cd.if_exist_province == False:
                        base = ['区站号', '经度', '纬度', '观测场拔海高度']
                    else:
                        base = ['区站号', '省份', '站名', '经度', '纬度', '观测场拔海高度']
                    for year in years:
                        base.append(str(year) + '年')
                    ws.append(base)  # 添加首行
                    cd.fun_cal = {"平均值": mean, "最大值": max, "最小值": min, "中位数": median, "标准差": pstdev, "众数": mode, '求和': sum}  # 计算函数
                    all_result = {}  # 存放文件中存在的年份的所有数据
                    for year, values in self.data.items():  # 一年一年计算
                        for station, vs in values.items():
                            if station not in all_result:   #
                                all_result[station] = self.other_infos[station]
                            data = []
                            for v in vs:  # 这个for用于找出纯数字的数据
                                v = str(v)
                                if v.isdigit() == True or "-" in v or "." in v:
                                    data.append(float(v))
                            if data != []:
                                if self.data_type == '变化幅度':
                                    result = cd.fun_cal['最大值'](data) - cd.fun_cal['最小值'](data)
                                else:
                                    try:
                                        result = cd.fun_cal[self.data_type](data)
                                    except Exception :
                                        result = '不唯一'
                            else:
                                result = '缺失'
                            all_result[station] += [result]
                    for station, values in all_result.items():
                        ws.append(values)

                    cells = ws['E' + "2" + ":" + get_column_letter(ws.max_column) + str(ws.max_row)]  # 单元格范围E2到最后
                    for row in cells:
                        for col in row:
                            col.number_format = "0.00"  # 都设置为保留两位小数
                    save_file = cd.file_out + "\\" + os.path.basename(cd.file_in) + "\\" + "{}.xlsx".format(data_name)
                    if os.path.exists(cd.file_out + "\\" + os.path.basename(cd.file_in)) == False:
                        os.mkdir(cd.file_out + "\\" + os.path.basename(cd.file_in))
                    wb.save(save_file)
                    print("写入完毕!")
        if len(cd.two_data) != 0:
            Data.start_twice_cal(self)  # 处理完毕后，进行二次处理



    @get_abnormal
    def start_twice_cal(self):
        print("二次计算处理中...")
        for name, values in cd.targetInfo.items():
            for day_to_day_type in values:
                k = re.findall(r'(\w+日)', day_to_day_type)[0]
                stand_file_name = k + name + day_to_day_type.split(k)[1]
                file_name = cd.file_out + "\\" + os.path.basename(cd.file_in) + '\\' + stand_file_name +".xlsx"
                print("正在处理 %s" % file_name)
                wb = load_workbook(file_name, data_only=True)
                ws = wb[wb.sheetnames[0]]  # 一个表格只有一个sheetname
                first_line = [x.value for x in ws["A1:{}1".format(get_column_letter(ws.max_column))][0]]
                for k in first_line:  # 获取第一个年所在的位置
                    if '年' in k and "多年" not in k:
                        first_year = get_column_letter(first_line.index(k) + 1)
                        break
                for k in first_line[::-1]:  # 获取最后一个年所在的位置
                    if k is not None and '年' in k and "多年" not in k:
                        last_year_index = first_line.index(k) + 1
                        last_year = get_column_letter(first_line.index(k) + 1)
                        break
                for v in cd.two_data:
                    ws['{}1'.format(get_column_letter(last_year_index + cd.two_data.index(v) + 1))].value = v # 给后续加上统计指标
                for two_type in cd.two_data:
                    for row in range(2, ws.max_row + 1):
                        Range = "{}{}:{}{}".format(first_year, row, last_year, row)
                        data = []
                        for v in ws[Range][0]:
                            v = str(v.value)
                            if v.isdigit() == True or "-" in v or "." in v:
                                data.append(float(v))   # 只处理数字
                        if data != []:  # 如果data有数据
                            if two_type == '变化范围值':
                                result = cd.fun_cal['最大值'](data) - cd.fun_cal['最小值'](data)
                            elif two_type == '方差':
                                result = math.pow(cd.fun_cal['标准差'](data), 2.00)
                            else:
                                try:
                                    result = cd.fun_cal[two_type](data)
                                except Exception:
                                    result = '不唯一'
                        else:
                            result = '缺失'
                        ws[get_column_letter(last_year_index + cd.two_data.index(two_type) + 1) + str(row)].value = result
                cells = ws[first_year + "2" + ":" + get_column_letter(ws.max_column) + str(ws.max_row)]  # 单元格范围E2到最后
                for row in cells:
                    for col in row:
                        col.number_format = "0.00"  # 都设置为保留两位小数
                wb.save(file_name)
        print("二次计算完毕!")
    # 年， 类型， 写入data的key， 从excel内获取数据的sheetname
    @get_abnormal
    def processDataNew(self, year, TYPE, name, ws_name):
        if ((int(year) % 4 == 0 and int(year) % 100 != 0) or (int(year) % 400 == 0)) == False and self.day_leap == 29:
            self.day2 = 28  # 如果不是闰年，并且选择了29日，将不是闰年的day改为29
        if ((int(year) % 4 == 0 and int(year) % 100 != 0) or (int(year) % 400 == 0)) and self.day_leap == 29:  # 如果为闰年
            self.day2 = self.day_leap  # 赋值为29
        base_excel_file = os.path.dirname(cd.file_in) + "\\" + os.path.basename(cd.file_in) + "\\{}_{}{}ok.xlsx"
        for month in range(self.month_pre, self.month_next + 1):
            month = str(month) if month >= 10 else ('0' + str(month))
            excel_file = base_excel_file.format(TYPE, year, month)
            month = int(month)

            if os.path.exists(excel_file) == True:  # 如果文件存在
                print("处理文件： %s " % excel_file)
                wb = load_workbook(excel_file)
                ws = wb[ws_name]

                first_line = ws["A" + "1" + ":" + get_column_letter(ws.max_column) + "1"]  # A1:AK1
                first_line = [x.value for x in first_line[0]]   # 获取值['区站号', '经度', '纬度', '观测场拔海高度', '年', '月'
                first_line.insert(0, '-1')  # 在第一个插入一个无关紧要的数值，用于后续查找信息

                for row in range(2, ws.max_row+1):
                    station = ws['A' + str(row)].value   # 获取区站号
                    if station not in self.other_infos.keys():
                        temp = [x.value for x in ws[get_column_letter(first_line.index("区站号")) + str(row) + ":" + get_column_letter(first_line.index("观测场拔海高度")) + str(row)][0]]
                        cd.if_exist_province = True if len(temp) > 4 else False
                        self.other_infos[station] = temp  # 插入数据
                    if station not in self.data[year].keys():
                        self.data[year][station] = []
                    Data.getValue(self, ws, month, station, row, first_line, year)  # 插入数据
            else:
                print(excel_file,"不存在！")

    @get_abnormal
    def getValue(self, ws, month, station, row, first_line, year):
        day_num = [0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]   # 每月的数量
        if (int(year) % 4 == 0 and int(year) % 100 != 0) or (int(year) % 400 == 0):   # 如果该年是闰年
            day_num[2] = 29  # 将2月设置为29天
        if month == self.month_pre:  # 如果是开始的月份，那么
            if self.month_pre == self.month_next:
                for day in range(self.day1, self.day2 + 1):  # 处理第一个月到结束
                    Data.append_data(self, ws, first_line, row, year, month, day, station)
            else:
                for day in range(self.day1, day_num[month] + 1):  # 处理第一个月到结束
                    Data.append_data(self, ws, first_line, row, year, month, day, station)
        elif self.month_pre < month < self.month_next:  # 如果不是开始的月份, 如果是在月份中
            for day in range(1, day_num[month] + 1):
                Data.append_data(self, ws, first_line, row, year, month, day, station)
        else:
            for day in range(1, self.day2 + 1):
                Data.append_data(self, ws, first_line, row, year, month, day, station)

    def append_data(self, ws, first_line, row, year, month, day, station):
        try:
            v = ws[get_column_letter(first_line.index('%s日' % day)) + str(row)].value  # 获取对应的日的值
            self.data[year][station].append(v)
        except Exception as e:
            # print("对于区站号:{}, {}年{}月{}日数据可能不存在。".format(station, year, month, day))
            return False

    @get_abnormal
    def getYear(self):
        years = []
        for file in os.listdir(cd.file_in):
            TYPE = re.findall(r'(\w+)_', file)[0]
            k = re.findall(r'_(\d{4})(\d+)', file)[0]
            year, month = k[0], int(k[1])
            if self.month_pre <= month <= self.month_next:
                years.append(year)  # 将该年份存进去
            years = list(set(years))
        return sorted(years), TYPE       # 从小到大排列
